/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.elcom.emocbc.dbmapping.persistence;

import com.elcom.emocbc.dbmapping.tables.daos.RptDoanhthuBcDao;
import com.elcom.emocbc.dbmapping.tables.pojos.RptDoanhthuBc;
import com.google.common.collect.Lists;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
import org.jooq.conf.StatementType;
import org.jooq.impl.DefaultConfiguration;

/**
 *
 * @author GiangLT
 */
public class RptDoanhthuBcPersistence extends RptDoanhthuBcDao {

    public RptDoanhthuBcPersistence() {
        ConnectionProvider connectionProvider = new DbConnectionProvider();
        Settings settings = new Settings();
        settings.setStatementType(StatementType.STATIC_STATEMENT);
        Configuration configuration = new DefaultConfiguration()
                .set(connectionProvider)
                .set(SQLDialect.ORACLE)
                .set(settings);
        this.setConfiguration(configuration);
    }

    public RptDoanhthuBc findByTime(Date time) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "SELECT * FROM RPT_DOANHTHU_BC WHERE trunc(time) = trunc(?)";
//            String sql = "select decode(dk_moi,null,' ',dk_moi) dk_moi, decode(tl_dk_moi_homqua,null,' ',tl_dk_moi_homqua) tl_dk_moi_homqua";
//            sql += ", decode(clickers,null,' ',clickers) clickers, decode(tl_click_homqua,null,' ',tl_click_homqua) tl_click_homqua";
//            sql += ", decode(dk_ngay,null,' ',dk_ngay) dk_ngay, decode(tl_dk_ngay_homqua,null,' ',tl_dk_ngay_homqua) tl_dk_ngay_homqua";
//            sql += ", decode(huy_ngay,null,' ',huy_ngay) huy_ngay, decode(tl_huy_ngay_homqua,null,' ',tl_huy_ngay_homqua) tl_huy_ngay_homqua";
//            sql += ", decode(san_luong_tong,null,' ',san_luong_tong) san_luong_tong, decode(TL_SAN_LUONG_TONG_HOMQUA,null,' ',TL_SAN_LUONG_TONG_HOMQUA) TL_SAN_LUONG_TONG_HOMQUA";
//            sql += ", decode(doanhthu,null,' ',doanhthu) doanhthu, decode(tl_doanhthu_homqua,null,' ',tl_doanhthu_homqua) tl_doanhthu_homqua";
//            sql += ", decode(doanhthu_mt,null,' ',doanhthu_mt) doanhthu_mt, decode(tl_doanhthu_mt_homqua,null,' ',tl_doanhthu_mt_homqua) tl_doanhthu_mt_homqua";
//            sql += ", decode(tong_doanhthu,null,' ',tong_doanhthu) tong_doanhthu, decode(tl_tong_doanhthu_homqua,null,' ',tl_tong_doanhthu_homqua) tl_tong_doanhthu_homqua";
//            sql += ", decode(luy_ke_thang,null,' ',luy_ke_thang) luy_ke_thang, decode(tl_luy_ke_thang_homqua,null,' ',tl_luy_ke_thang_homqua) tl_luy_ke_thang_homqua";
//            sql += " from rpt_doanhthu_bc ";
//            sql += " where trunc(time) = trunc(?)";
            con = DbAccess.getInstance().getConnection();
            ps = con.prepareStatement(sql);
            ps.setTimestamp(1, new Timestamp(time.getTime()));

            rs = ps.executeQuery();
            RptDoanhthuBc doanhthuBc = new RptDoanhthuBc();
            if (rs.next()) {
                int i = 1;
                doanhthuBc.setTime(rs.getTimestamp(i++));

                doanhthuBc.setDkMoi(rs.getBigDecimal(i++));
                doanhthuBc.setClickers(rs.getBigDecimal(i++));
                doanhthuBc.setDkNgay(rs.getBigDecimal(i++));
                doanhthuBc.setHuyNgay(rs.getBigDecimal(i++));
                doanhthuBc.setDoanhthu(rs.getBigDecimal(i++));

                doanhthuBc.setTlDkMoiHomqua(rs.getBigDecimal(i++));
                doanhthuBc.setTlDkMoiThangtruoc(rs.getBigDecimal(i++));
                doanhthuBc.setTlDkMoiNamtruoc(rs.getBigDecimal(i++));
                doanhthuBc.setTlClickHomqua(rs.getBigDecimal(i++));
                doanhthuBc.setTlClickThangtruoc(rs.getBigDecimal(i++));

                doanhthuBc.setTlClickNamtruoc(rs.getBigDecimal(i++));
                doanhthuBc.setTlDoanhthuHomqua(rs.getBigDecimal(i++));
                doanhthuBc.setTlDkNgayHomqua(rs.getBigDecimal(i++));
                doanhthuBc.setTlHuyNgayHomqua(rs.getBigDecimal(i++));
                doanhthuBc.setSanLuongTong(rs.getBigDecimal(i++));

                doanhthuBc.setDoanhthuMt(rs.getBigDecimal(i++));
                doanhthuBc.setTlDoanhthuMtHomqua(rs.getBigDecimal(i++));
                doanhthuBc.setTongDoanhthu(rs.getBigDecimal(i++));
                doanhthuBc.setTlTongDoanhthuHomqua(rs.getBigDecimal(i++));
                doanhthuBc.setTlTongDoanhthuThangtruoc(rs.getBigDecimal(i++));

                doanhthuBc.setTlTongDoanhthuNamtruoc(rs.getBigDecimal(i++));
                doanhthuBc.setLuyKeThang(rs.getBigDecimal(i++));
                doanhthuBc.setTlLuyKeThangHomqua(rs.getBigDecimal(i++));
                doanhthuBc.setTlSanLuongTongHomqua(rs.getBigDecimal(i++));

            }
            return doanhthuBc;
        } finally {
            DbUtils.closeQuietly(con, ps, rs);
        }
    }
}
